use CreativeSystems;
GO

ALTER PROC pr_GetEventsReportData
(
	@EventType VARCHAR(MAX),
	@PackageType VARCHAR(MAX),
	@ClientName VARCHAR(MAX),
	@DateRangeStart DATETIME,
	@DateRangeEnd DATETIME
)
AS
	SELECT e.Title
		,c.FirstName + ' ' + c.LastName "Client Name"
		,p.PackageType
		,et.EventTypeName
		,e.StartDate "Date"
	FROM [Event] e
	INNER JOIN Package p ON p.PackageID = e.PackageID
	INNER JOIN EventType et ON et.EventTypeID = e.EventTypeID
	INNER JOIN Client c ON c.ClientID = e.ClientID
	where et.EventTypeName = COALESCE(@EventType, et.EventTypeName)
	AND p.PackageType = COALESCE(@PackageType, p.PackageType)
	AND e.StartDate >= @DateRangeStart AND e.StartDate <= @DateRangeEnd
	AND c.FirstName + ' ' + c.LastName = COALESCE(@ClientName, c.FirstName + ' ' + c.LastName)
	Order By e.StartDate;